﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TruckWatch.Cloud.Models;
using System.Data.SqlClient;
using System.Data;

namespace TruckWatch.Cloud.DAL
{
    public class DriverDAL
    {
        #region DriverSummary

        public static List<DriverSummary> GetAllDriverSummaries()
        {
            ParamBuilder pb = new ParamBuilder();

            string sql = @"
select 
    Id,UserId,Name,TruckType
from 
    Driver";

            List<DriverSummary> summaries = new List<DriverSummary>();
            using (SqlDataReader reader = DataGeneral.ExecuteReader(sql, null, false))
            {
                while (reader.Read())
                {
                    DriverSummary driver = new DriverSummary();
                    driver.Id = (int)reader["Id"];
                    driver.UserId = reader["UserId"] as string;
                    //summary.UserIdHyperLinked = string.Format("<a href='/'>{0}</a>",summary.UserId);
                    driver.Name = reader["Name"] as string;
                    driver.TruckType = reader["TruckType"] as string;
                    summaries.Add(driver);
                }
            }
            return summaries;
        }

        #endregion
        
        #region Get Driver(s)


        public static DriverNEF GetDriverById(int id)
        {
            ParamBuilder pb = new ParamBuilder();
            pb.AddParam(System.Data.SqlDbType.VarChar, "@Id", id);

            string sql = @"
select 
    Id,UserId,PassHash,Name,TruckType
from 
    Driver
where 
    Id=@Id
";

            DriverNEF driver = null;
            using (SqlDataReader reader = DataGeneral.ExecuteReader(sql, pb.Parameters, false))
            {
                if (reader.Read())
                {
                    driver = PopulateDriver(reader);
                }
            }
            return driver;
        }
        
        public static DriverNEF GetDriverByUserId(string userId)
        {
            ParamBuilder pb = new ParamBuilder();
            pb.AddParam(System.Data.SqlDbType.VarChar, "@UserId", userId);

            string sql = @"
select 
    Id,UserId,PassHash,Name,TruckType
from 
    Driver
where 
    UserId=@UserId
";

            DriverNEF driver = null;
            using (SqlDataReader reader = DataGeneral.ExecuteReader(sql, pb.Parameters, false))
            {
                if (reader.Read())
                {
                    driver = PopulateDriver(reader);
                }
            }
            return driver;
        }

        public static List<DriverNEF> GetAllDrivers()
        {

            string sql = @"
select 
    Id,UserId,PassHash,Name,TruckType 
from 
    Driver";

            List<DriverNEF> drivers = new List<DriverNEF>();
            using (SqlDataReader reader = DataGeneral.ExecuteReader(sql, null, false))
            {
                while (reader.Read())
                {
                    drivers.Add(PopulateDriver(reader));
                }
            }

            return drivers;
        }

        private static DriverNEF PopulateDriver(SqlDataReader reader)
        {
            DriverNEF driver = new DriverNEF();
            driver.Id = (int)reader["Id"];
            driver.UserId = reader["UserId"] as string;
            driver.PassHash = reader["PassHash"] as string;
            driver.Name = reader["Name"] as string;
            driver.TruckType = reader["TruckType"] as string;

            return driver;
        }

        #endregion

        #region UpdateDriver

        public static void UpdateDriver(int id, string name,string truckType)
        {
            ParamBuilder pb = new ParamBuilder();
            pb.AddParam(SqlDbType.Int, "@Id", id);
            pb.AddParam(SqlDbType.VarChar, "@Name", name);
            pb.AddParam(SqlDbType.VarChar, "@TruckType", truckType);

            string sql = @"
update Driver 
    set Name = @Name,
    TruckType = @TruckType
where
    Id = @Id
";

            DataGeneral.ExecuteNonQuery(sql, pb.Parameters, false);
        }

        #endregion

        #region Add Driver

        public static void AddDriver(DriverNEF driver)
        {
            ParamBuilder pb = new ParamBuilder();
            pb.AddParam(SqlDbType.VarChar, "@UserId", driver.UserId);
            pb.AddParam(SqlDbType.VarChar, "@PassHash", driver.PassHash);
            pb.AddParam(SqlDbType.VarChar, "@Name", driver.Name);
            pb.AddParam(SqlDbType.VarChar, "@TruckType", driver.TruckType);

            string sql = @"
INSERT INTO Driver
           (UserId,PassHash,Name,TruckType)
     VALUES
           (@UserId,@PassHash,@Name,@TruckType)
";

            DataGeneral.ExecuteNonQuery(sql, pb.Parameters, false);
        }

        #endregion
    }
}


